package util;

import entity.UserEntity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 * @author 嫣然
 */
public class Info {

    private static Connection conn=null;
    private static PreparedStatement pst=null;
    private static ResultSet rs=null;

    public static int userlLogin(int id,String password){
        int count=0;
        try {
            conn=DataBaseConnection.getConnection();
            String sql="select COUNT(*) from card where id=? and password=?";
            pst=conn.prepareStatement(sql);
            pst.setInt(1,id);
            pst.setString(2,password);
            rs=pst.executeQuery();
            rs.next();
            count=rs.getInt(1);
        }catch (Exception e){
            e.printStackTrace();
            System.out.println("登录出错");
        }
        return count;
    }

    public static int addUser(int id,String student,String password,int grande){
        int count=0;
        try {
            conn=DataBaseConnection.getConnection();
            String sql="insert into card(id,student,password,grande) values (?,?,?,?) ";
            pst=conn.prepareStatement(sql);
            pst.setInt(1,id);
            pst.setString(2,student);
            pst.setString(3,password);
            pst.setInt(4,grande);
            count=pst.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            System.out.println("用户注册出错");
        }
        return count;
    }

    public static int setUser(int id,String student,String password,int grande,int whereid){
        int count=0;
        try {
            conn=DataBaseConnection.getConnection();
            String sql="UPDATE card SET`id` = ?,student =?, `password` = ?, `grande` = ? WHERE `id` = ?";
            pst=conn.prepareStatement(sql);
            pst.setInt(1,id);
            pst.setString(2,student);
            pst.setString(3,password);
            pst.setInt(4,grande);
            pst.setInt(5,whereid);
            count=pst.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            System.out.println("修改信息出错");
        }
        return count;
    }
    //充值饭卡 是否充值成功
    public static int cardmoney(int cardmoney,int id){
        int count=0;
        try {
            conn=DataBaseConnection.getConnection();
            String sql="UPDATE `test`.`card` SET `cardmoney` = ? where id=?";
            pst=conn.prepareStatement(sql);
            pst.setInt(1,cardmoney);
            pst.setInt(2,id);
            count=pst.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            System.out.println("充值出错");
        }
        return count;
    }
    //查询余额
    public static int yue(int id){
        int money = 0;
        try {
            conn=DataBaseConnection.getConnection();
            String sql="SELECT cardmoney FROM card WHERE id=?;";
            pst=conn.prepareStatement(sql);
            pst.setInt(1,id);
            rs=pst.executeQuery();
            rs.next();
            int cardmoney=rs.getInt("cardmoney");
            money=cardmoney;
        }catch (Exception e){
            e.printStackTrace();
            System.out.println("查询余额出错");
        }
        return money;
    }
    //消费
    public static int usemoney(int use,int id){
        int count=0;
        try {
            conn=DataBaseConnection.getConnection();
            String sql="UPDATE `test`.`card` SET `cardmoney` = ? WHERE `id` = ?;";
            pst=conn.prepareStatement(sql);
            pst.setInt(1,use);
            pst.setInt(2,id);
            count=pst.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            System.out.println("消费出现错误");
        }
        return count;
    }
    //查询银行卡余额
    public static int money(int id){
        int money1 = 0;
        try {
            conn=DataBaseConnection.getConnection();
            String sql="SELECT money FROM card WHERE id=?;";
            pst=conn.prepareStatement(sql);
            pst.setInt(1,id);
            rs=pst.executeQuery();
            rs.next();
            int cardmoney=rs.getInt("money");
            money1=cardmoney;
        }catch (Exception e){
            e.printStackTrace();
            System.out.println("余额查询错误");
        }
        return money1;
    }
    //更新银行卡
    public static int setMoney(int setMoney,int id){
        int money1 = 0;
        try {
            conn=DataBaseConnection.getConnection();
            String sql="UPDATE `test`.`card` SET `money`= ? WHERE `id` =?;";
            pst=conn.prepareStatement(sql);
            pst.setInt(1,setMoney);
            pst.setInt(2,id);
            money1=pst.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            System.out.println("充值出现错误！");
        }
        return money1;
    }

    //查询信息
    public static ArrayList<UserEntity> selectUser(int id){
        ArrayList<UserEntity> list=new ArrayList<>();
        UserEntity userEntity=new UserEntity();
        try {
            conn=DataBaseConnection.getConnection();
            String sql="select id,student,grande from card where id=?";
            pst=conn.prepareStatement(sql);
            pst.setInt(1,id);
            rs= pst.executeQuery();
            while (rs.next()){
                int id2=rs.getInt("id");
                String stu=rs.getNString("student");
                int grande1=rs.getInt("grande");
                userEntity.setId(id2);
                userEntity.setStudent(stu);
                userEntity.setGrande(grande1);
                list.add(userEntity);
            }
        }catch (Exception e){
            e.printStackTrace();
            System.out.println("信息显示错误");
        }
        return list;
    }
}
